package com.lijinjiang.dao;

import com.lijinjiang.entity.User;
import com.lijinjiang.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 用户与数据库交互类，与用户相关的数据库操作
 */
public class UserDao {

    // 通过账号获取用户
    public User selectUserByAccount(String account) {
        User user = null;
        // 获取数据库连接
        Connection conn = DBUtil.getConn();
        // 声明句柄对象
        PreparedStatement stmt = null;
        // 声明操作对象
        ResultSet rs = null;
        // 定义查询语句
        String sql = "select * from user where account=?";
        try {
            // 获取句柄
            stmt = conn.prepareStatement(sql);
            // 设置句柄拼接
            stmt.setString(1, account);
            rs = stmt.executeQuery();
            while (rs.next()) {
                user = new User();
                user.setAccount(rs.getString("account"));
                user.setName(rs.getString("name"));
                user.setPassword(rs.getString("password"));
                user.setCollege(rs.getString("college"));
                user.setSex(rs.getByte("sex"));
                user.setAvatarSrc(rs.getString("avatar_src"));
                user.setPhone(rs.getString("phone"));
                user.setAdmin(rs.getBoolean("is_admin"));
                // System.out.println(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            DBUtil.free(rs, stmt, conn);
        }
        return user;
    }

    // 更新用户信息
    public boolean updateUser(User user) {
        boolean res = false;
        // 获取数据库连接
        Connection conn = DBUtil.getConn();
        // 声明句柄对象
        PreparedStatement stmt = null;
        // 声明操作对象
        ResultSet rs = null;
        // 定义查询语句
        String sql = "update user set name=?,password=?,college=?,sex=?,avatar_src=?,phone=?,is_admin=? where account=?";
        try {
            // 获取句柄
            stmt = conn.prepareStatement(sql);
            //设置句柄拼接
            stmt.setString(1, user.getName());
            stmt.setString(2, user.getPassword());
            stmt.setString(3, user.getCollege());
            stmt.setByte(4, user.getSex());
            stmt.setString(5, user.getAvatarSrc());
            stmt.setString(6, user.getPhone());
            stmt.setBoolean(7, user.getAdmin());
            stmt.setString(8, user.getAccount());
            if (stmt.executeUpdate() == 1){
                res = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.free(stmt, conn);
        }
        return res;
    }

    // 根据SQL获取用户信息
    public List<User> selectUserBySql(String sql, String sqlKey) {
        List<User> list = new ArrayList<>();
        // 获取数据库连接
        Connection conn = DBUtil.getConn();
        // 声明句柄对象
        PreparedStatement stmt = null;
        // 声明操作对象
        ResultSet rs = null;
        User user = null;
        try {
            // 获取操作句柄
            stmt = conn.prepareStatement(sql);
            if (sqlKey != null && sqlKey.length() != 0) {
                // 拼接语句
                stmt.setString(1, sqlKey);
            }
            // 获取操作对象
            rs = stmt.executeQuery();
            while (rs.next()) {
                user = new User();
                user.setAccount(rs.getString("account"));
                user.setName(rs.getString("name"));
                user.setPassword(rs.getString("password"));
                user.setCollege(rs.getString("college"));
                user.setSex(rs.getByte("sex"));
                user.setAvatarSrc(rs.getString("avatar_src"));
                user.setPhone(rs.getString("phone"));
                user.setAdmin(rs.getBoolean("is_admin"));
                // 把用户添加至列表
                list.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            DBUtil.free(rs, stmt, conn);
        }
        return list;
    }

    // 添加用户
    public boolean insertUser(User user) {
        boolean res = false;
        // 获取数据库连接
        Connection conn = DBUtil.getConn();
        // 声明句柄对象
        PreparedStatement stmt = null;
        // 声明操作对象
        ResultSet rs = null;
        // 定义查询语句
        String sql = "insert into user(account, name, password, college, sex, avatar_src, phone, is_admin) values(?, ?, ?, ?, ?, ?, ?, ?)";
        try {
            // 获取句柄
            stmt = conn.prepareStatement(sql);
            //设置句柄拼接
            stmt.setString(1, user.getAccount());
            stmt.setString(2, user.getName());
            stmt.setString(3, user.getPassword());
            stmt.setString(4, user.getCollege());
            stmt.setByte(5, user.getSex());
            stmt.setString(6, user.getAvatarSrc());
            stmt.setString(7, user.getPhone());
            stmt.setBoolean(8, user.getAdmin());
            if (stmt.executeUpdate() == 1){
               res = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.free(stmt, conn);
        }
        return res;
    }

    // 删除用户
    public boolean deleteUserByAccount(String account) {
        boolean res = false;
        // 获得数据库连接
        Connection conn = DBUtil.getConn();
        // 声明句柄对象
        PreparedStatement stmt = null;
        String sql = "delete from user where account = ?";
        try {
            // 获得操作句柄
            stmt = conn.prepareStatement(sql);
            //拼接语句
            stmt.setString(1, account);
            //执行
            if(stmt.executeUpdate() == 1) {
                res = true;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            // 一定要做的事，释放连接
            DBUtil.free(stmt, conn);
        }
        return res;
    }
}
